package com.util;

import com.Column;
import com.ExcelHelper;
import com.entity.Book;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.SQLOutput;
import java.util.*;
import java.util.logging.Logger;

/**
 * title:excel工具类
 * author:ljj
 * date:2019-03-28
 */
public class ExcelUtils {
    //excel的尾缀
    private static String xlsx = "xlsx";
    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            System.out.println("文件名不是excel格式");
            return false;
        }
        return true;
    }
    /**
     * 读EXCEL文件，获取信息集合
     *
     * @return
     */
    public static <T> List<T> getExcelInfo(File mFile, T t) {
        String fileName = mFile.getName();// 获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            Workbook wb = null;
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                    wb = new XSSFWorkbook(new FileInputStream(mFile));
            }else{
                    wb = new HSSFWorkbook(new FileInputStream(mFile));
            }
                return readExcelValue(wb, t);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取实体类
     * @param student
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> T imports(T student, Map<String,Object> map) throws Exception {
        StringBuilder sb = new StringBuilder();
        Class<?> clazz = student.getClass();
        boolean ExcelHelperAnnoExits = clazz.isAnnotationPresent(ExcelHelper.class);
        // 判断 class 对象上是否有 @ExcelHelperAnnoExits 注解
        if (!ExcelHelperAnnoExits) {
            return null;
        }
        // 获取类
        ExcelHelper table = clazz.getAnnotation(ExcelHelper.class);
        // 获取 class 对象中的所有字段并遍历
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            boolean fieldAnnoExits = field.isAnnotationPresent(Column.class);
            // 判断字段上是否含有 @Column 注解
            if (!fieldAnnoExits) {
                continue;
            }
            // 获取注解中的字段名
            Column column = field.getAnnotation(Column.class);
            String columnName = column.value();
            // 通过 get() 方法获取传入的字段值
            String fieldName = field.getName();
            String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase()
                    + fieldName.substring(1);
           // Method method = clazz.getMethod(getMethodName);
            Method method =  clazz.getDeclaredMethod(setMethodName, new Class[]{field.getType()});
            method.invoke(student,new Object[]{getTypeValue(field.getType(),map.get(columnName))});
        }

        return student;
    }
    /**
     * 读取Excel里面用户的信息
     *
     * @param wb
     * @return
     */
    private static <T> List<T> readExcelValue(Workbook wb,T a)throws ClassNotFoundException,IllegalAccessException,InstantiationException {
        List<T> list = new ArrayList<T>();
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        int totalCells = 0;
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
           totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            Class clazz = Class.forName(a.getClass().getName());
            T s = (T) clazz.newInstance();
            Map<String,Object> map = new HashMap<String, Object>();
            // 循环Excel的列
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    map.put(String.valueOf(c),cell.getStringCellValue());
                }
            }

            try {
                 s =imports(s,map);
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 添加到list
            list.add(s);
        }
        return list;
    }

    /**
     * 返回对应的类型数据
     * @param typeClass
     * @param value
     * @return
     */
    public static Object getTypeValue(Class<?> typeClass, Object value){
        if(typeClass == int.class  || value instanceof Integer){
            if(null == value){
                return 0;
            }
            return value;
        }else if(typeClass == short.class){
            if(null == value){
                return 0;
            }
            return value;
        }else if(typeClass == byte.class){
            if(null == value){
                return 0;
            }
            return value;
        }else if(typeClass == double.class){
            if(null == value){
                return 0;
            }
            return value;
        }else if(typeClass == long.class){
            if(null == value){
                return 0;
            }
            return value;
        }else if(typeClass == String.class){
            if(null == value){
                return "";
            }
            return value;
        }else if(typeClass == boolean.class){
            if(null == value){
                return true;
            }
            return value;
        }else if(typeClass == BigDecimal.class){
            if(null == value){
                return new BigDecimal(0);
            }
            return new BigDecimal(value+"");
        }else {
            return typeClass.cast(value);
        }
    }

    /**
     *
     * @param filename
     * @param sheetname
     * @param list
     * @param <T>
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public static <T> void exportExcel(String filename,String sheetname,List<T> list) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, IOException {
        //先获取实体对象
        Class clazz =  null;
        List<String> titleList = new LinkedList<String>();
        List<Map<String,Object>> contentList = new LinkedList<Map<String, Object>>();
        for (T mode :list){
            clazz = mode.getClass();
            Map<String,Object> map =new HashMap<String, Object>();
            Field[] fieldArray = clazz.getDeclaredFields();
            for (Field field: fieldArray) {
                Column column = field.getAnnotation(Column.class);
                String setMethodName = "get" + field.getName().substring(0, 1).toUpperCase()
                        + field.getName().substring(1);
                Method method = clazz.getDeclaredMethod(setMethodName);
                map.put(column.value(),method.invoke(mode));
            }
            contentList.add(map);
        }
        boolean ExcelHelperAnnoExits = clazz.isAnnotationPresent(ExcelHelper.class);
        // 判断 class 对象上是否有 @ExcelHelperAnnoExits 注解
        if (!ExcelHelperAnnoExits) {
            System.out.println("ExcelHelper[info]-----------------------无法识别实体对象");
        }
        Field[] fields = clazz.getDeclaredFields();
        for (Field field: fields) {
            Map<String,Object> map =new HashMap<String, Object>();
            boolean flag = field.isAnnotationPresent(Column.class);
            if(flag){
               Column column = field.getAnnotation(Column.class);
               titleList.add(column.title());
           }
        }
        HSSFWorkbook ws = createExcel(titleList,fields.length,contentList,filename);
        File file = new File("D:\\334.xls");
        OutputStream out = new FileOutputStream(file);
        ws.write(out);
        out.flush();
        out.close();
    }

    /**
     * 创建表格对象
     * @param heads
     * @param fieldnum
     * @param dataList
     * @param sheettName
     * @return
     */
    public  static  HSSFWorkbook createExcel(List<String> heads,int fieldnum, List<Map<String,Object>> dataList,String sheettName) {
        HSSFWorkbook  workbook =new HSSFWorkbook();

        if(sheettName==null||"".equals(sheettName))
            sheettName="sheet"+Math.random();

        HSSFSheet sheet = workbook.createSheet(sheettName); //
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(20);
        // 在索引0的位置创建行（最顶端的行）
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell=null;

        //创建表格第一行的标题
        for (int i = 0; i < heads.size(); i++) {
            // 在索引0的位置创建单元格（左上端）
            cell = row.createCell(i);
            // 定义单元格为字符串类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 在单元格中输入一些内容
            cell.setCellValue(heads.get(i));
        }
        //循环赋值
        for (int n = 0; n < dataList.size(); n++) {
            // 在索引1的位置创建行（最顶端的行）
            HSSFRow row_value = sheet.createRow(n + 1);
            Map<String, Object> dataMap =dataList.get(n);
            HSSFCell cell_v=null;
            for (int i = 0; i < fieldnum; i++) {
                // 在索引0的位置创建单元格（左上端）
                cell_v = row_value.createCell(i);
                // 定义单元格为字符串类型
                cell_v.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 在单元格中输入一些内容
                cell_v.setCellValue(dataMap.get(String.valueOf(i))==null?"":dataMap.get(String.valueOf(i)).toString());
            }
        }
        return  workbook ;
    }
}
